<?php
namespace app\examScore\controller;
use app\common\Base;
use app\common\Excel;
use think\Db;
//--学生各科成绩
class StudentSubjectScore extends Base {
	
	public function index() {
		return $this->fetch();
	}

	public function pageQuery() {
		$termId = input("termId");
		$examId = input("examId");
		$grade = input("grade");
		if($grade) {
			$array = explode("_", $grade);
			$grade = $array[0];
		}
		$gradeId = input("gradeId");
		$clazz = input("clazz");
		$page = input("page");
		$rows = input("rows");
		$sort = input("sort");
		$order = input("order");
		$orderby = "班级,学号";
		if($sort) $orderby = $sort." ".$order;
		if(!$examId) return [];
		if($clazz) $where["班级"] = intval($gradeId)*100+intval($clazz);
		$examCount = Db::table("考试名称")
					 ->where("学期ID=".$termId." and 年级ID=".$gradeId)
					 ->count();
		$preExamId = 0;
		if(!($examCount == 1 && ($gradeId==1||$gradeId==4))) {
			if($examCount == 1) {
				$preWhere["年级ID"] = array("eq",intval($gradeId) - 1);
				$preWhere["学期ID"] = array("lt", $termId);
			}else {
				$preWhere["年级ID"] = array("eq", $gradeId);
				$preWhere["ID"] = array("lt", $examId);
			}
			$preWhere["考试类型"] = array("eq", 1);
			$preExam = Db::table("考试名称")
						 ->field("max(ID) ID")
						 ->where($preWhere)
						 ->find();
			$preExamId = $preExam["ID"];
		}
		$where["考试ID"] = $examId;
		$subsql = Db::table("成绩".$grade)
				  ->field("学生ID,级排 as 上次级排")
				  ->where("考试ID=".$preExamId)
				  ->buildSql();
		$result = Db::table("成绩".$grade)->alias("a")
				  ->join([$subsql=>'b'],"a.学生ID=b.学生ID","LEFT")
				  ->field("班级,学号,姓名,语文,语文名次,数学,数学名次,英语,英语名次,物理,物理名次,化学,化学名次,政治,政治名次,历史,历史名次,政史,政史名次,体育,体育名次,总分,班排,级排,上次级排,上次级排-级排 as 升幅")
				  ->where($where)
				  ->order($orderby)
				  ->page($page, $rows)
				  ->select();
		$count = Db::table("成绩".$grade)->alias("a")
				  ->where($where)
				  ->count();
		$grid["rows"] = selectResult($result);
		$grid["total"] = $count;
		return $grid;
	}

	public function exportExcel() {
		$termId = input("termId");
		$examId = input("examId");
		$grade = input("grade");
		if($grade) {
			$array = explode("_", $grade);
			$grade = $array[0];
		}
		$gradeId = input("gradeId");
		$clazz = input("clazz");
		$page = input("page");
		$rows = input("rows");
		if(!$examId) return [];
		if($clazz) $where["班级"] = intval($gradeId)*100+intval($clazz);
		$examCount = Db::table("考试名称")
					 ->where("学期ID=".$termId." and 年级ID=".$gradeId)
					 ->count();
		$preExamId = 0;
		if(!($examCount == 1 && ($gradeId==1||$gradeId==4))) {
			if($examCount == 1) {
				$preWhere["年级ID"] = array("eq",intval($gradeId) - 1);
				$preWhere["学期ID"] = array("lt", $termId);
			}else {
				$preWhere["年级ID"] = array("eq", $gradeId);
				$preWhere["ID"] = array("lt", $examId);
			}
			$preWhere["考试类型"] = array("eq", 1);
			$preExam = Db::table("考试名称")
						 ->field("max(ID) ID")
						 ->where($preWhere)
						 ->find();
			$preExamId = $preExam["ID"];
		}
		$where["考试ID"] = $examId;
		$subsql = Db::table("成绩".$grade)
				  ->field("学生ID,级排 as 上次级排")
				  ->where("考试ID=".$preExamId)
				  ->buildSql();
		$result = Db::table("成绩".$grade)->alias("a")
				  ->join([$subsql=>'b'],"a.学生ID=b.学生ID","LEFT")
				  ->field("班级,学号,姓名,语文,语文名次,数学,数学名次,英语,英语名次,物理,物理名次,化学,化学名次,政治,政治名次,历史,历史名次,政史,政史名次,体育,体育名次,总分,班排,级排,上次级排,上次级排-级排 as 升幅")
				  ->where($where)
				  ->order("班级,学号")
				  ->select();
		$result = selectResult($result);
		//excel表头内容
		$fields = "班级,学号,姓名,语文,语文名次,数学,数学名次,英语,英语名次,".(($gradeId==1) ? "" : "物理,物理名次,").(($gradeId>=3) ? "化学,化学名次," : "")."政治,政治名次,历史,历史名次,政史,政史名次,体育,体育名次,总分,班排,级排,上次级排,升幅";
		$list = explode(",", $fields);
		$header = [];
		foreach ($list as $key=> $value){
			$header[$value] = $value;
		}
		Excel::toExcel($result,'学生各科成绩',$header);
		exit;
	}

}
